﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using WS1.Models;

namespace WS1.Repository
{
	public class ProductsRepository:IDisposable
	{
		private readonly SqlConnection _sqlConnection;
		public ProductsRepository()
		{
            string connStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
            _sqlConnection = new SqlConnection(connStr);
			_sqlConnection.Open();

        }
        public IEnumerable<Products> All()
        {
            var sql = @"SELECT [product_id]
      ,[product_name]
      ,[product_category]
      ,[ingredients]
      ,[current_price]
      ,[production_cost]
      ,[is_currently_available]
      ,[is_seasonal]
      ,[launch_date]
      ,[Desciption]


      
  FROM [BelleCroissantLyormais].[dbo].[Products]";
            var products = new List<Products>();
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var product = new Products()
                        {
                            product_id = reader.GetInt32(0),
                            product_name = reader.GetString(1),
                            product_category = reader.GetString(2),
                            ingredients = reader.GetString(3),
                            current_price = reader.GetDecimal(4),
                            production_cost = reader.GetDecimal(5),
                            is_seasonal = reader.GetBoolean(6),
                            is_currently_available = reader.GetBoolean(7),
                            launch_date = reader.GetDateTime(8),
                            Desciption = reader.IsDBNull(9) ? string.Empty : reader.GetString(9)
                        };
                        products.Add(product);
                    }
                }
            }
            return products;
        }

        public Products Single(int id)
        {
            var sql = @"SELECT [product_id]
      ,[product_name]
      ,[product_category]
      ,[ingredients]
      ,[current_price]
      ,[production_cost]
      ,[is_currently_available]
      ,[is_seasonal]
      ,[launch_date]
      ,[Desciption]


      
  FROM [BelleCroissantLyormais].[dbo].[Products]
                          WHERE [product_id] = @product_id";
            Products product = null;
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@product_id", id);

                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        product = new Products()
                        {
                            product_id = reader.GetInt32(0),
                            product_name = reader.GetString(1),
                            product_category = reader.GetString(2),
                            ingredients = reader.GetString(3),
                            current_price = reader.GetDecimal(4),
                            production_cost = reader.GetDecimal(5),
                            is_seasonal = reader.GetBoolean(6),
                            is_currently_available = reader.GetBoolean(7),
                            launch_date = reader.GetDateTime(8),
                            Desciption = reader.IsDBNull(9) ? string.Empty : reader.GetString(9)

                        };
                    }
                }

            }
            return product;
        }

        public bool Add(Products product)
        {
            //product_id 主键。修改成自增
            var sql = @"
INSERT INTO [dbo].[products]
           ([product_name],
            [product_category],
            [ingredients],
            [current_price],
            [production_cost],
            [is_seasonal],
            [is_currently_available],
            [launch_date],
            [Desciption]
           
            ) 
VALUES (@product_name,
        @product_category,
        @ingredients,
        @current_price,
        @production_cost,
        @is_seasonal,
        @is_currently_available,
        @launch_date,
        @Desciption
      
        )";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@product_name", product.product_name);
                cmd.Parameters.AddWithValue("@product_category", product.product_category);
                cmd.Parameters.AddWithValue("@ingredients", product.ingredients);
                cmd.Parameters.AddWithValue("@current_price", product.current_price);
                cmd.Parameters.AddWithValue("@production_cost", product.production_cost);
                cmd.Parameters.AddWithValue("@is_seasonal", product.is_seasonal);
                cmd.Parameters.AddWithValue("@is_currently_available", product.is_currently_available);
                cmd.Parameters.AddWithValue("@launch_date", product.launch_date);
                cmd.Parameters.AddWithValue("@Desciption", product.Desciption);




                return cmd.ExecuteNonQuery() == 1;
            }
        }

        public bool Update(int id, Products product)
        {
            var sql = @"UPDATE [dbo].[products]
                           SET [product_name] = @product_name
                              ,[product_category] = @product_category
                              ,[ingredients] = @ingredients
                              ,[current_price] = @current_price
                              ,[production_cost] = @production_cost
                              ,[is_seasonal] = @is_seasonal
                              ,[is_currently_available] = @is_currently_available
                              ,[launch_date] = @launch_date
                              ,[Desciption]=@Desciption
                         WHERE  [product_id] = @product_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@product_id", id);
                cmd.Parameters.AddWithValue("@product_name", product.product_name);
                cmd.Parameters.AddWithValue("@product_category", product.product_category);
                cmd.Parameters.AddWithValue("@ingredients", product.ingredients);
                cmd.Parameters.AddWithValue("@current_price", product.current_price);
                cmd.Parameters.AddWithValue("@production_cost", product.production_cost);
                cmd.Parameters.AddWithValue("@is_seasonal", product.is_seasonal);
                cmd.Parameters.AddWithValue("@is_currently_available", product.is_currently_available);
                cmd.Parameters.AddWithValue("@launch_date", product.launch_date);
                cmd.Parameters.AddWithValue("@Desciption", product.Desciption);


                return cmd.ExecuteNonQuery() == 1;
            }
        }

        public bool RemoveSingle(int id)
        {
            var sql = @"DELETE [dbo].[products]
                         WHERE  [product_id] = @product_id";
            using (var cmd = new SqlCommand(sql, _sqlConnection))
            {
                cmd.Parameters.AddWithValue("@product_id", id);
                return cmd.ExecuteNonQuery() == 1;
            }
        }
        public void Dispose()
        {
            _sqlConnection.Close(); 
            _sqlConnection.Dispose();
        }
    }
}